package com.ipartek.pruebas.bbdd;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MostrarAlumnosBDP5 {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DriverManager.getConnection("jdbc:odbc:pruebas", ",", ",");
			stmt = conn.createStatement();
		} catch (SQLException e) {
			System.out.println("No conecta la base de datos");
		}
		try {

			String sql = "select * from Alumnos where Edad;";
			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				System.out.print("Nombre:  " + rs.getString("Nombre"));
				System.out.print(", ");
				System.out.println("Edad: " + rs.getInt("Edad"));
			}
			System.out.println();
			//pruebas sobre selects con edad
			sql= "select count(id) as Cuenta from Alumnos where Edad>50;";
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				System.out.println("Numero de alumnos mayores de 50: " + rs.getString("Cuenta"));
			}
			sql= "select count(id) as Cuenta from Alumnos where Edad<18;";
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				System.out.println("Numero de alumnos menores de 18: " + rs.getString("Cuenta"));
			}
			sql= "select count(id) as Cuenta from Alumnos where Edad>=20 and Edad<=30;";
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				System. out.println("Numero de alumnos entre 20 y 30: " + rs.getString("Cuenta"));
			}
			//pruebas sobre selects con nombre
			sql= "select count(id) as Cuenta from Alumnos where Nombre like('%AB%');";
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				System. out.println("Numero de alumnos con AB: " + rs.getString("Cuenta"));
			}
			sql= "select count(id) as Cuenta from Alumnos where Nombre like('%FR%');";
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				System. out.println("Numero de alumnos con FR: " + rs.getString("Cuenta"));
			}
			stmt.close();
			conn.close();
		} catch (SQLException ep) {
			// TODO Auto-generated catch block
			ep.printStackTrace();
			System.out.println("Error de select");
		} catch (Exception es) {
			es.printStackTrace();
		}
		
	}

}
